package com.mtoolkit.jdbc;

import java.sql.Connection;
import java.util.concurrent.Future;

import com.mtoolkit.jdbc.handler.ResultSetHandler;
import com.mtoolkit.page.Page;

/**
 * A template interface that wrapped jdbc-api to operate database.
 * 
 * @author 	<a href="mailto:xishizhang@gmail.com">ZhangShixi</a>
 * @version 1.0, 12/7/2011
 * @since 	JDK1.5
 * 
 * @see		com.mtoolkit.jdbc.handler.ResultSetHandler
 */
public interface JdbcTemplate {

	// ---- insert ----------------------------------------------------------------
	/**
     * Executes insert operation with specified {@code sql} statement and return 
     * the primary key of the newly insert row.
     *
     * @param  conn 	 current database connection.
     * @param  keySql	 an get primary key SQL statement(used for oracle sequence).
     * @param  insertSql an insert SQL statement that may contain one or more '?' 
     * 		   			 in parameter place holders.
     * 
     * @return newly insert row primary key.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} of {@code sql} is null.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
	public Number executeKeyInsert(Connection conn,
								   String keySql,
								   String insertSql);
	
	/**
     * Executes insert operation with specified {@code sql} statement and 
     * {@code params} witch will be filled into parameter place holders in 
     * SQL statement, it returns the primary key of the newly insert row.
     *
     * @param  conn   	 current database connection.
     * @param  keySql	 an get primary key SQL statement(used for oracle sequence).
     * @param  insertSql an insert SQL statement that may contain one or more '?' 
     * 		   			 in parameter place holders.
     * @param  params 	 SQL statement parameter list.
     * 
     * @return newly insert row primary key.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
	public Number executeKeyInsert(Connection conn,
								   String keySql,
								   String insertSql, 
								   Object... params);
	
	/**
     * Executes batch insert operation with specified {@code sql} statement and 
     * {@code params} witch will be filled into parameter place holders in SQL 
     * statement, it returns the primary key array of the newly insert rows.
     * 
     * @param  conn   	 current database connection.
     * @param  keySql	 an get primary key SQL statement(used for oracle sequence).
     * @param  insertSql an insert SQL statement that may contain one or more '?' 
     * 		   			 in parameter place holders.
     * @param  params 	 SQL statement parameter list.
     * 
     * @return newly insert rows primary key array.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if ervey number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
	public Number[] executeKeyInsert(Connection conn, 
									 String keySql,
									 String insertSql,
									 Object[][] params);
	
	/**
     * Executes batch insert operation with specified {@code sql} statement and 
     * {@code params} witch will be filled into parameter place holders in SQL 
     * statement and specified {@code batchSize}, 
     * it returns the primary key array of the newly insert rows.
     * 
     * @param  conn   	 current database connection.
     * @param  keySql	 an get primary key SQL statement(used for oracle sequence).
     * @param  insertSql an insert SQL statement that may contain one or more '?' 
     * 		   			 in parameter place holders.
     * @param  params 	 SQL statement parameter list.
     * @param  batchSize execute batch size.
     * 
     * @return newly insert rows primary key array.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if ervey number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
	public Number[] executeKeyInsert(Connection conn, 
									 String keySql,
									 String insertSql,
									 Object[][] params, 
									 int batchSize);
	
	// ---- insert/update/delete --------------------------------------------------
	/**
     * Executes insert/update/delete operation with specified {@code sql} statement.
     *
     * @param  conn current database connection.
     * @param  sql  an SQL statement that may contain one or more '?' 
     * 		   		in parameter place holders.
     * 
     * @return row count for SQL Data Manipulation Language (DML) statements,
     *         or 0 for SQL statements that return nothing.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
	public int executeUpdate(Connection conn, 
							 String sql);
    
	/**
     * Executes insert/update/delete operation with specified {@code sql} statement 
     * and {@code params} witch will be filled into parameter place holders 
     * in SQL statement.
     *
     * @param  conn   current database connection.
     * @param  sql 	  an SQL statement that may contain one or more '?' 
     * 		   		  in parameter place holders.
     * @param  params SQL statement parameter list.
     * 
     * @return row count for SQL Data Manipulation Language (DML) statements,
     *         or 0 for SQL statements that return nothing.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
    public int executeUpdate(Connection conn, 
    						 String sql, 
    						 Object... params);
    /**
     * Executes batch insert/update/delete operation with specified {@code sql} 
     * statement and {@code params} witch will be filled into parameter place 
     * holders in SQL statement.
     * 
     * @param  conn   current database connection.
     * @param  sql 	  an SQL statement that may contain one or more '?' 
     * 		   		  in parameter place holders.
     * @param  params SQL statement parameter list.
     * 
     * @return row count array for SQL Data Manipulation Language (DML) statements,
     *         or 0 for SQL statements that return nothing. 
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if ervey number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
    public int[] executeBatchUpdate(Connection conn, 
    								String sql, 
    								Object[][] params);
    
    /**
     * Executes batch insert/update/delete operation with specified {@code sql} 
     * statement and {@code params} witch will be filled into parameter place 
     * holders in SQL statement and specified {@code batchSize}.
     * 
     * @param  conn   	 current database connection.
     * @param  sql 	  	 an SQL statement that may contain one or more '?' 
     * 		   		  	 in parameter place holders.
     * @param  params 	 SQL statement parameter list.
     * @param  batchSize execute batch size.
     * 
     * @return row count array for SQL Data Manipulation Language (DML) statements,
     *         or 0 for SQL statements that return nothing. 
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if ervey number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
    public int[] executeBatchUpdate(Connection conn, 
    								String sql, 
    								Object[][] params, 
    								int batchSize);
    
    // ---- delay insert/update/delete --------------------------------------------
    /**
     * Executes delay insert/update/delete operation with specified {@code sql} 
     * statement.
     * 
     * @param  conn current database connection.
     * @param  sql  an SQL statement that may contain one or more '?' 
     * 		   		in parameter place holders.
     * 
     * @return a future instance of row count for SQL Data Manipulation Language 
     * 		   (DML) statements, or 0 for SQL statements that return nothing.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
    public Future<Integer> executeDelayUpdate(Connection conn, 
    										  String sql);
    
    /**
     * Executes delay insert/update/delete operation with specified {@code sql} 
     * statement and {@code params} witch will be filled into parameter place 
     * holders in SQL statement.
     *
     * @param  conn   current database connection.
     * @param  sql 	  an SQL statement that may contain one or more '?' 
     * 		   		  in parameter place holders.
     * @param  params SQL statement parameter list.
     * 
     * @return a future instance of row count for SQL Data Manipulation Language 
     * 		   (DML) statements, or 0 for SQL statements that return nothing.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
    public Future<Integer> executeDelayUpdate(Connection conn, 
    										  String sql, 
    										  Object... params);
    
    /**
     * Executes delay batch insert/update/delete operation with specified 
     * {@code sql} statement and {@code params} witch will be filled into 
     * parameter place holders in SQL statement.
     * 
     * @param  conn   current database connection.
     * @param  sql 	  an SQL statement that may contain one or more '?' 
     * 		   		  in parameter place holders.
     * @param  params SQL statement parameter list.
     * 
     * @return a future instance of row count array for SQL Data Manipulation 
     * 		   Language (DML) statements, or 0 for SQL statements that return 
     * 		   nothing. 
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if ervey number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
    public Future<Integer[]> executeBatchDelayUpdate(Connection conn, 
    												 String sql, 
    												 Object[][] params);
    
    /**
     * Executes delay batch insert/update/delete operation with specified 
     * {@code sql} statement and {@code params} witch will be filled into 
     * parameter place holders in SQL statement and specified {@code batchSize}.
     * 
     * @param  conn   	 current database connection.
     * @param  sql 	  	 an SQL statement that may contain one or more '?' 
     * 		   		  	 in parameter place holders.
     * @param  params 	 SQL statement parameter list.
     * @param  batchSize execute batch size.
     * 
     * @return a future instance of row count array for SQL Data Manipulation 
     * 		   Language (DML) statements, or 0 for SQL statements that return 
     * 		   nothing. 
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if ervey number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException	
     * 		   if a database access error occurs.
     */
    public Future<Integer[]> executeBatchDelayUpdate(Connection conn, 
    												 String sql, 
    												 Object[][] params, 
    												 int batchSize);
    
    // ---- query -----------------------------------------------------------------
    /**
     * Executes query operation with specified {@code sql} statement and result set 
     * call back handler.
     * 
     * @param  <T> 	   result type.
     * @param  conn	   current database connection.
     * @param  sql 	   an SQL statement that may contain one or more '?' 
     * 		   		   in parameter place holders.
     * @param  handler result set call back handler.
     * 
     * @return query result.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} or {@code handler} is null.
     * @throws PersistenceException 
     * 		   if a database access error occurs.
     */
    public <T> T executeQuery(Connection conn, 
    						  String sql, 
    						  ResultSetHandler<T> handler);
    
    /**
     * Executes query operation with specified {@code sql} statement and 
     * {@code params} witch will be filled into parameter place holders in SQL 
     * statement and result set call back handler.
     * 
     * @param  <T> 	   result type.
     * @param  conn	   current database connection.
     * @param  sql 	   an SQL statement that may contain one or more '?' 
     * 		   		   in parameter place holders.
     * @param  handler result set call back handler.
     * @param  params  SQL statement parameter list.
     * 
     * @return query result.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} or {@code handler} is null.
     * @throws IllegalArgumentException 
     * 		   if number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException 
     * 		   if a database access error occurs.
     */
    public <T> T executeQuery(Connection conn, 
    						  String sql, 
    						  ResultSetHandler<T> handler, 
    						  Object... params);
    
    /**
     * Executes row count query operation with specified {@code sql} statement.
     * 
     * @param  conn	   current database connection.
     * @param  sql 	   an SQL statement that may contain one or more '?' 
     * 		   		   in parameter place holders.
     * 
     * @return row count.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws PersistenceException 
     * 		   if a database access error occurs.
     */
    public long executeCountQuery(Connection conn, 
    							  String sql);
    
    /**
     * Executes row count query operation with specified {@code sql} statement 
     * and {@code params} witch will be filled into parameter place holders in 
     * SQL statement.
     * 
     * @param  conn	  current database connection.
     * @param  sql 	  an SQL statement that may contain one or more '?' 
     * 		   		  in parameter place holders.
     * @param  params SQL statement parameter list.
     * 
     * @return row count.
     * 
     * @throws NullPointerException 
     * 		   if {@code conn} or {@code sql} is null.
     * @throws IllegalArgumentException 
     * 		   if number of {@code params} is not match number of '?' 
     * 		   in SQL statement place holders.
     * @throws PersistenceException 
     * 		   if a database access error occurs.
     */
    public long executeCountQuery(Connection conn, 
    							  String sql, 
    							  Object... params);
    
    /**
     * Executes page query operation with specified SQL statement, 
     * page info and result set call back handler.
     * 
     * @param  <T>	    result type.
     * @param  conn	    current database connection.
     * @param  countSql row count SQL statement.
     * @param  querySql data query SQL statement.
     * @param  page	    page info.
     * @param  handler  result set call back handler.
     * 
     * @return the specified page data result.
     * 
     * @throws NullPointerException 
     * 		   if one of the conn/countSql/querySql/page/handler is null.
     * @throws PersistenceException 
     * 		   if a database access error occurs.
     */
    public <T> T executePageQuery(Connection conn, 
    							  String countSql, 
    							  String querySql, 
    							  Page<?> page, 
    							  ResultSetHandler<T> handler);
    
    /**
     * Executes page query operation with specified SQL statement, page info, 
     * result set call back handler and parameters witch will be filled into 
     * parameter place holders in countSql/querySql statement.
     * 
     * @param  <T>	    result type.
     * @param  conn	    current database connection.
     * @param  countSql row count SQL statement.
     * @param  querySql data query SQL statement.
     * @param  page	    page info.
     * @param  handler  result set call back handler.
     * @param  params	SQL statement parameter list.
     * 
     * @return the specified page data result.
     * 
     * @throws NullPointerException 
     * 		   if one of the conn/countSql/querySql/page/handler is null.
     * @throws IllegalArgumentException 
     * 		   if number of {@code params} is not match number of '?' 
     * 		   in countSql/querySql statement place holders.
     * @throws PersistenceException 
     * 		   if a database access error occurs.
     */
    public <T> T executePageQuery(Connection conn, 
    							  String countSql, 
    							  String querySql, 
    							  Page<?> page, 
    							  ResultSetHandler<T> handler, 
    							  Object... params);
    
    /**
     * Executes page query operation with specified SQL statement, page info, 
     * result set call back handler and parameters witch will be filled into 
     * parameter place holders in countSql/querySql statement..
     * 
     * @param  <T>	     result type.
     * @param  conn	     current database connection.
     * @param  countSql  row count SQL statement.
     * @param  querySql  data query SQL statement.
     * @param  page	     page info.
     * @param  handler   result set call back handler.
     * @param  limitSize limit size of the query data list, 
     * 		   			 do not set query limit sizes if it is non-positive.
     * @param  params	 SQL statement parameter list.
     * 
     * @return the specified page data result.
     * 
     * @throws NullPointerException 
     * 		   if one of the conn/countSql/querySql/page/handler is null.
     * @throws IllegalArgumentException 
     * 		   if number of {@code params} is not match number of '?' 
     * 		   in countSql/querySql statement place holders.
     * @throws PersistenceException 
     * 		   if a database access error occurs.
     */
    public <T> T executePageQuery(Connection conn, 
						    	  String countSql, 
						    	  String querySql, 
						    	  Page<?> page, 
						    	  ResultSetHandler<T> handler, 
						    	  int limitSize, 
						    	  Object... params);
    
}

